package anbook.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import anbook.dao.IBoardDAO;
import anbook.dao.IBoardLikeDAO;
import anbook.dao.IBoardPictureDAO;
import anbook.dao.IBoardReply;
import anbook.db.Database;
import anbook.dto.Board;
import anbook.dto.Board_like;
import anbook.dto.Board_picture;
import anbook.dto.Board_reply;

public class BoardDAOImpl implements IBoardDAO, IBoardLikeDAO, IBoardPictureDAO, IBoardReply {
	
	
	
	@Override
	public Board getBoard(int idx) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Board board = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_GET_BOARD;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1,idx);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				board = new Board();
				
				board.setB_num(rs.getInt("b_num"));
				board.setB_title(rs.getString("b_title"));
				board.setB_content(rs.getString("b_content"));
				board.setB_visible(rs.getString("b_visible"));
				board.setB_wdate(rs.getTimestamp("b_wdate"));
				board.setMb_email(rs.getString("mb_email"));
			}
		}catch (Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, rs);
		}
		return board;
	}

	@Override
	public int insertBoard(Board board) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int ret = -1;
		
		try{
			con = Database.Open();
			
			String sql = SQL_INSERT_BOARD;
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, board.getMb_email());
			pstmt.setString(2, board.getB_title());
			pstmt.setString(3, board.getB_content());
			
			pstmt.execute();
			pstmt.close();
			
			sql = SQL_LAST_ID;
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if (rs.next()){
				ret = rs.getInt(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, rs);
		}

		return ret;
	}

	@Override
	public void deleteBoard(int idx) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_DELETE_BOARD;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, idx);
			
			pstmt.execute();
		}catch(Exception e){
			
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public void updateBoard(Board board) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_UPDATE_BOARD;
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, board.getB_title());
			pstmt.setString(2, board.getB_content());
			pstmt.setString(3, board.getB_visible());
			pstmt.setInt(4, board.getB_num());
			
			pstmt.execute();
		}catch(Exception e){
			
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public List<Board_picture> getBoardPictures(int idx) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Board_picture> board_pictures = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_GET_PICTURES;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, idx);
			
			rs = pstmt.executeQuery();
			
			board_pictures = new ArrayList<Board_picture>();
			
			while(rs.next()){
				Board_picture picture = new Board_picture();
				
				picture.setBp_id(rs.getInt("bp_id"));
				picture.setB_num(rs.getInt("b_num"));
				picture.setPic_path(rs.getString("pic_path"));
				
				board_pictures.add(picture);
			}
		}catch (Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, rs);
		}
		
		return board_pictures;
	}

	@Override
	public void deleteBoardPicture(Board_picture pic) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_DELETE_PICTURE;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, pic.getB_num());
			pstmt.setInt(2, pic.getBp_id());
			
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public void insertBoardPicture(List<Board_picture> list) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_INSERT_PICTURE;
			pstmt = con.prepareStatement(sql);

			for(Board_picture pic : list){
				pstmt.setInt(1, pic.getB_num());
				pstmt.setString(2, pic.getPic_path());
				pstmt.execute();
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public void insertBoardReply(Board_reply reply) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_INSERT_BOARDREPLY;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, reply.getB_num());
			pstmt.setString(2, reply.getMb_email());
			pstmt.setString(3, reply.getRe_context());
			pstmt.setInt(4, reply.getRe_ref());
			pstmt.setInt(5, reply.getRe_step());
			
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public void deleteBoardReply(int idx) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_DELETE_BOARDREPLY;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, idx);
			
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public List<Board_reply> getBoardReplys(int idx) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Board_reply> board_replys = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_GET_BOARDREPLYS;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, idx);
			
			rs = pstmt.executeQuery();
			
			board_replys = new ArrayList<Board_reply>();
			
			while(rs.next()){
				Board_reply reply = new Board_reply();
				
				reply.setRe_num(rs.getInt("re_num"));
				reply.setMb_email(rs.getString("mb_email"));
				reply.setRe_context(rs.getString("re_context"));
				reply.setB_num(rs.getInt("b_num"));
				reply.setRe_ref(rs.getInt("re_ref"));
				reply.setRe_step(rs.getInt("re_step"));
				reply.setRe_wdate(rs.getTimestamp("re_wdate"));
				
				board_replys.add(reply);
			}
		}catch (Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, rs);
		}
		
		return board_replys;
	}

	@Override
	public void updateBoardReply(Board_reply reply) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_UPDATE_BOARDREPLY;
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, reply.getRe_context());
			pstmt.setInt(2, reply.getRe_num());

			pstmt.execute();
		}catch(Exception e){
			
		}finally{
			Database.Close(con, pstmt, null);
		}
	}
	
	public List<Board_like> getBoardLikes(int idx){
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Board_like> board_like = null;
		
		try{
			con = Database.Open();
			String sql = SQL_GET_BOARDLIKES;
			
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, idx);
			
			rs = pstmt.executeQuery();
			
			board_like = new ArrayList<Board_like>();
			
			while(rs.next()){
				((Board) board_like).setB_num(rs.getInt("b_num"));
				((Board) board_like).setMb_email(rs.getString("mb_email"));
				((Board_like) board_like).setBl_type(rs.getString("bl_type"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, rs);
		}
		return null;
	}

	@Override
	public void insertLike(Board_like like) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_INSERT_BOARDLIKE;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, like.getB_num());
			pstmt.setString(2, like.getMb_email());
			pstmt.setString(3, like.getBl_type());
			
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public void deleteLike(int idx) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_DELETE_BOARDLIKE;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, idx);
			
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public int countLike(Board_like board) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int cnt = 0;

		try{
			con = Database.Open();
			String sql = SQL_GETCOUNT_BOARDLIKE;
			pstmt = con.prepareStatement(sql);
			
			if(rs.next()){
				cnt = rs.getInt(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, rs);
		}
		return cnt;
	}

	@Override
	public void allDeleteLike(int idx) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_ALLDELETE_BOARDLIKE;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, idx);
			
			pstmt.execute();
		}catch(Exception e){
			
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public void allDeleteBoardReply(int idx) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_ALLDELETE_BOARDREPLY;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, idx);
			
			pstmt.execute();
		}catch(Exception e){
			
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public void allDeleteBoardPicture(int idx) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_ALLDELETE_BOARDPICTURE;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, idx);
			
			pstmt.execute();
		}catch(Exception e){
			
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public Board_picture getBoardPicture(int idx) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Board_picture picture = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_GET_PICTURE;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1,idx);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				picture = new Board_picture();
				
				picture.setB_num(rs.getInt("b_num"));
				picture.setBp_id(rs.getInt("bp_id"));
				picture.setPic_path(rs.getString("pic_path"));
			}
		}catch (Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, rs);
		}
		
		return picture;
	}
}
